﻿USE [sms]
GO
if exists(select name from sysobjects where name ='cp_insert_user' and type = 'P')
drop procedure cp_insert_user
go 
create procedure [dbo].[cp_insert_user]
@CIL nvarchar(20),--输入参数，发件人
@nameCN varchar(30),
@nameEN varchar(30),
@email varchar(50),
@isActive bit,
@departmentID int,
@returns int output    --返回值
as
--首先判断传入参数是否为空
if(@CIL != '' and @nameCN != '' and @nameEN != '' and @email != '' and len(@isActive) != 0)
	begin
		--判断CIL，email是否已存在
		declare @name varchar(20),@youjian varchar(50)
		set @name = ''
		set @youjian = ''
		select @name = CIL from [User] where CIL = @CIL
		select @youjian = Email from [User] where Email = @email
		if(len(@name) = 0 and len(@youjian) = 0)
			begin
				declare @did int --接收部门ID
				set @did =-1
				--查询@departmentID是否存在，存在判断是否有记录
				if(len(@departmentID) != 0)
					begin
						select @did = DepartmentID From Department where DepartmentID = @departmentID
						if(@did != -1)
							begin
								--插入成功返回0
								insert into [user] (CIL,NameCN,NameEN,Email,IsActive,DepartmentID) values(@CIL,@nameCN,@nameEN,@email,'True',@departmentID)
								set @returns = 0
							end
						--不存在这个部门时，返回2
						else
							begin
								set @returns = 2
							end
					end
				--没有传入时直接插入记录
				else 
					begin
						--插入成功返回0
						insert into [user] (CIL,NameCN,NameEN,Email,IsActive,DepartmentID) values(@CIL,@nameCN,@nameEN,@email,'True',@departmentID)
						set @returns = 0
					end
			end
		 --CIL，Email已存在时返回3
		else
			begin
				set @returns = 3
			end
	end
--存在空值时返回1
else 
	begin
		set @returns = 1
	end
